package dao;

import bean.Area;
import bean.Goods;
import bean.Message;
import bean.People;
import util.JDBCutil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * Created by young on 2017/6/28.
 */
public class OtherDao {

    private Connection connection = JDBCutil.getConnection();

    public List<Message> searchInfo(String message) {
        List<Message> list = new ArrayList<>();
        String text = "%" + message + "%";
        String sql = "SELECT * FROM goods, people, area WHERE area.name LIKE ? OR people.name LIKE ? OR goods.name LIKE ? AND goods.people_id=people.id AND goods.area_id=area.id AND people.area_id=area.id";
        try {
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, text);
            ps.setString(2, text);
            ps.setString(3, text);
            ResultSet set = ps.executeQuery();
            while (set.next()) {
                Message message1 = new Message();
                Area area = new Area();
                area.setId(set.getInt("area.id"));
                area.setName(set.getString("area.name"));
                message1.setArea(area);
                Goods goods = new Goods();
                goods.setId(set.getInt("goods.id"));
                goods.setName(set.getString("goods.name"));
                goods.setSummary(set.getString("summary"));
                message1.setGoods(goods);
                People people = new People();
                people.setId(set.getInt("people.id"));
                people.setName(set.getString("people.name"));
                people.setAge(set.getString("people.age"));
                people.setSex(set.getString("people.sex"));
                message1.setPeople(people);
                list.add(message1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

}
